<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Localities extends CI_Model {

	function __construct() {
        parent::__construct();
    }

    public function exists($region, $locality) {
    	$id = self::getId($region, $locality);
    	return ($id > 0);
    }

    public function getAll($region) {
    	$sql = "SELECT " .
			   "l.LocalityId AS `Id`, " .
			   "l.RegionId, " .
			   "r.Region, " .
			   "lg.LocalityGroupId, " .
			   "CAST(IFNULL(lg.LocalityGroup, '') AS char(150)) AS `LocalityGroup`," .
			   "l.Locality, " .
			   "CAST(CASE WHEN l.IsCity = 1 THEN 1 WHEN l.IsProvince = 1 THEN 2 ELSE 0 END AS signed) AS `LocalityType`, " .
			   "l.Description, " .
			   "FROM " .
			   "localities AS l " .
			   "LEFT OUTER JOIN localitygroups AS lg ON l.LocalityGroupId = lg.LocalityGroupId " .
			   "INNER JOIN regions AS r ON l.RegionId = r.RegionId " .
			   "INNER JOIN regiongroups AS rg ON r.RegionGroupId = rg.RegionGroupId " .
			   "WHERE " .
			   "(rg.CountryId = " . COUNTRY_ID . ") " .
			   (trim($region) != ''? " AND (l.`RegionId` IN(" . $region . ")) " : "") .
			   "ORDER BY " .
			   "`Region`, " .
			   "`Locality`";

		$query = $this->db->query($sql);
		return $query->result_array();
    }

    public function getEverything() {
        $sql = "SELECT " .
               "l.LocalityId AS `Id`, " .
               "l.RegionId, " .
               "r.Region, " .
               "lg.LocalityGroupId, " .
               "CAST(IFNULL(lg.LocalityGroup, '') AS char(150)) AS `LocalityGroup`," .
               "l.Locality, " .
               "CAST(CASE WHEN l.IsCity = 1 THEN 1 WHEN l.IsProvince = 1 THEN 2 ELSE 0 END AS signed) AS `LocalityType`, " .
               "l.Description, " .
               "rg.CountryId " .
               "FROM " .
               "localities AS l " .
               "LEFT OUTER JOIN localitygroups AS lg ON l.LocalityGroupId = lg.LocalityGroupId " .
               "INNER JOIN regions AS r ON l.RegionId = r.RegionId " .
               "INNER JOIN regiongroups AS rg ON r.RegionGroupId = rg.RegionGroupId " .
               "ORDER BY " .
               "`Region`, " .
               "`Locality`";

        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function getId($region, $locality) {
    	$sql = "SELECT " .
    		   "l.LocalityId AS `Id` " .
    		   "FROM " .
    		   "`localities` AS l " .
    		   "WHERE " .
    		   "(l.RegionId = " . $region . ") AND " .
    		   "(l.Locality LIKE " . $this->db->escape((string)$locality) . ")";

    	$id = 0;
    	
    	$query = $this->db->query($sql);
    	if ($query->num_rows() > 0) {
    		$row = $query->row_array();
    		$id = $row['Id'];
    	}

    	return $id;
    }

    public function save($updates) {
    	$saved = true;

    	$this->db->trans_begin();

    	foreach ($updates as $update) {
    		$sql = '';
    		$params = array();

    		$isCity = 0;
    		$isProvince = 0;

    		if ($update->LocalityType == 1) $isCity = 1;
    		else {
    			if ($update->LocalityType == 2) $isProvince = 1;
    		}

    		if ($update->RowState == 'added') {
    			$localityGroupId = self::validateLocalityGroup($update->Region, $update->LocalityGroup);

    			$sql = "INSERT INTO `localities` " .
    				   "(`RegionId`, `LocalityGroupId`, `Locality`, `IsCity`, `IsProvince`, `Description`, `DateCreated`) " .
    				   "VALUES " .
    				   "(?, ?, ?, ?, ?, ?, NOW())";

    			$params = array( $update->Region, $localityGroupId, $update->Locality, $isCity, $isProvince, $update->Description);
    		}
    		else if ($update->RowState == 'modified') {
    			$localityGroupId = self::validateLocalityGroup($update->Region, $update->LocalityGroup);

    			$sql = "UPDATE `localities` SET " .
    				   "`RegionId` = ?, `LocalityGroupId` = ?, `Locality` = ?, `IsCity` = ?, `IsProvince` = ?, `Description` = ? " .
    				   "WHERE " .
    				   "(`LocalityId` = ?)";

    			$params = array( $update->Region, $localityGroupId, $update->Locality, $isCity, $isProvince, $update->Description, $update->Id );	
    		}
    		else if ($update->RowState == 'deleted') {
    			$sql = "DELETE FROM `localities` WHERE (`LocalityId` = ?)";
    			$params = array( $update->Id );
    		}
    		else {

    		}

    		if (trim($sql) != '') $this->db->query($sql, $params);
    	}

    	if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            $saved = false;
        }
        else {
            $this->db->trans_commit();
        }

    	return $saved;
    }

    public function validateLocalityGroup($region, $localityGroup) {
    	$id = 0;

    	$sql = "SELECT " .
    		   "lg.`LocalityGroupId` AS `Id` " .
    		   "FROM " .
    		   "`localitygroups` AS `lg` " .
    		   "WHERE " .
    		   "(lg.`RegionId` = " . $region . ") AND " .
    		   "(lg.`LocalityGroupId` = " . $localityGroup . ")";

    	$query = $this->db->query($sql);
    	if ($query->num_rows() > 0) $id = $localityGroup;

    	return $id;
    }

}